package com.idp.common.util;

import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

import com.idp.web.test.entity.DetailList;
import com.idp.web.test.entity.Out;
import com.idp.web.test.entity.Put;

/**
 * 操作Excel文件(模版为产品入库表)
 * 
 * @author yyh
 */
public class ExcelUtil {
	private static SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

	/**
	 * 构建put的导出excel
	 * 
	 * @param file
	 * @return
	 */
	public static HSSFWorkbook createPutWorkBook(List<Put> list) {
		// 创建workbook,对应一个excel文件
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 添加一个sheet
		HSSFSheet sheet = workbook.createSheet("入库信息表");
		// 添加表头(第0行)
		HSSFRow row = sheet.createRow(0);
		// 创建单元格,设置值表头,表头居中
		HSSFCellStyle style = getStyle(workbook).get("middel");
		HSSFCellStyle style2 = getStyle(workbook).get("middel_red");
		HSSFCellStyle style3 = getStyle(workbook).get("middel_green");
		HSSFCell cell = row.createCell(0);
		cell.setCellValue("产品名称");
		cell.setCellStyle(style);
		cell = row.createCell(1);
		cell.setCellValue("我司货号");
		cell.setCellStyle(style);
		cell = row.createCell(2);
		cell.setCellValue("入库时间");
		cell.setCellStyle(style);
		cell = row.createCell(3);
		cell.setCellValue("实际入库数量");
		cell.setCellStyle(style2);
		cell = row.createCell(4);
		cell.setCellValue("入库数量");
		cell.setCellStyle(style3);
		cell = row.createCell(5);
		cell.setCellValue("状态");
		cell.setCellStyle(style);
		cell = row.createCell(6);
		cell.setCellValue("入库仓库");
		cell.setCellStyle(style);
		String dataTime = "";
		for (int i = 0; i < list.size(); i++) {
			row = sheet.createRow(i + 1);
			cell = row.createCell(0);
			cell.setCellValue(list.get(i).getProduct().getName());
			cell.setCellStyle(style);

			cell = row.createCell(1);
			cell.setCellValue(list.get(i).getProduct().getCode());
			cell.setCellStyle(style);

			cell = row.createCell(2);
			if (list.get(i).getDatetime() != null) {
				dataTime = simpleDateFormat.format(list.get(i).getDatetime());
			}
			cell.setCellValue(dataTime);
			cell.setCellStyle(style);

			cell = row.createCell(3);
			cell.setCellValue(list.get(i).getRealbox());
			cell.setCellStyle(style2);

			cell = row.createCell(4);
			cell.setCellValue(list.get(i).getBox());
			cell.setCellStyle(style3);

			cell = row.createCell(5);
			cell.setCellValue(list.get(i).getPutStatus().getName());
			cell.setCellStyle(style);

			cell = row.createCell(6);
			cell.setCellValue(list.get(i).getWarehouse().getName());
			cell.setCellStyle(style);
		}
		return workbook;
	}

	/**
	 * 构建导出出库信息的excel
	 * 
	 * @param outs
	 * @return
	 */
	public static HSSFWorkbook createOutWorkBook(List<Out> outs) {
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("出库信息表");
		// 表头
		HSSFRow row = sheet.createRow(0);
		HSSFCellStyle style = workbook.createCellStyle();
		HSSFCellStyle style2 = getStyle(workbook).get("middel_red");
		HSSFCellStyle style3 = getStyle(workbook).get("middel_green");

		HSSFCell cell = row.createCell(0);
		cell.setCellValue("产品名称");
		cell.setCellStyle(style);
		cell = row.createCell(1);
		cell.setCellValue("我司货号");
		cell.setCellStyle(style);

		cell = row.createCell(2);
		cell.setCellValue("出库原因");
		cell.setCellStyle(style);

		cell = row.createCell(3);
		cell.setCellValue("出库时间");
		cell.setCellStyle(style);

		cell = row.createCell(4);
		cell.setCellValue("实际出库数量");
		cell.setCellStyle(style2);

		cell = row.createCell(5);
		cell.setCellValue("出库数量");
		cell.setCellStyle(style3);

		cell = row.createCell(6);
		cell.setCellValue("状态");
		cell.setCellStyle(style);

		cell = row.createCell(7);
		cell.setCellValue("出库仓库");
		cell.setCellStyle(style);

		String dataTime = "";
		for (int i = 0; i < outs.size(); i++) {
			row = sheet.createRow(i + 1);
			cell = row.createCell(0);
			cell.setCellValue(outs.get(i).getProduct().getName());
			cell.setCellStyle(style);

			cell = row.createCell(1);
			cell.setCellValue(outs.get(i).getProduct().getCode());
			cell.setCellStyle(style);

			cell = row.createCell(2);
			cell.setCellValue(outs.get(i).getOutCause().getName());
			cell.setCellStyle(style);

			if (outs.get(i).getDatetime() != null) {
				dataTime = simpleDateFormat.format(outs.get(i).getDatetime());
			}
			cell = row.createCell(3);
			cell.setCellValue(dataTime);
			cell.setCellStyle(style);

			cell = row.createCell(4);
			cell.setCellValue(outs.get(i).getRealbox());
			cell.setCellStyle(style2);

			cell = row.createCell(5);
			cell.setCellValue(outs.get(i).getMount());
			cell.setCellStyle(style3);

			cell = row.createCell(6);
			cell.setCellValue(outs.get(i).getOutStatus().getName());
			cell.setCellStyle(style);

			cell = row.createCell(7);
			cell.setCellValue(outs.get(i).getWarehouse().getName());
			cell.setCellStyle(style);
		}
		return workbook;
	}

	/**
	 * 构建明细导出excel
	 * 
	 * @param type
	 * @param mapList
	 * @return
	 */
	public static HSSFWorkbook creatDetailList(String type, Map<String, List<DetailList>> mapList) {
		HSSFWorkbook workbook = new HSSFWorkbook();
		switch (mapList.size()) {
		case 1:
			HSSFSheet sheet = workbook.createSheet(type);
			if ("入库".equals(type)) {
				workbook = createPutDetailList(workbook, sheet, mapList.get("入库"));
			} else {
				workbook = createOutDetailList(workbook, sheet, mapList.get("出库"));
			}
			break;
		case 2:
			HSSFSheet sheetPut = workbook.createSheet("入库明细表");
			/*System.out.println(mapList.get("入库").toString());
			for (int i = 0; i < mapList.get("入库").size(); i++) {
				System.out.println(mapList.get("入库").get(i).getPut().getProduct().getProductType());
			}*/
			workbook = createPutDetailList(workbook, sheetPut, mapList.get("入库"));
			HSSFSheet sheetOut = workbook.createSheet("出库明细表");
			workbook = createOutDetailList(workbook, sheetOut, mapList.get("出库"));
			break;
		default:
			break;
		}
		return workbook;
	}

	/**
	 * 创建出库明细workbook
	 * 
	 * @param workbook
	 * @param sheet
	 * @param list
	 * @return
	 */
	private static HSSFWorkbook createOutDetailList(HSSFWorkbook workbook, HSSFSheet sheet, List<DetailList> list) {
		// 表头
		HSSFRow row = sheet.createRow(0);
		HSSFCellStyle style = workbook.createCellStyle();
		HSSFCellStyle style3 = getStyle(workbook).get("middel_green");

		HSSFCell cell = row.createCell(0);
		cell.setCellValue("产品名称");
		cell.setCellStyle(style);

		cell = row.createCell(1);
		cell.setCellValue("我司货号");
		cell.setCellStyle(style);

		cell = row.createCell(2);
		cell.setCellValue("产品分类");
		cell.setCellStyle(style);

		cell = row.createCell(3);
		cell.setCellValue("出库时间");
		cell.setCellStyle(style);

		cell = row.createCell(4);
		cell.setCellValue("仓库名称");
		cell.setCellStyle(style);

		cell = row.createCell(5);
		cell.setCellValue("出库数量");
		cell.setCellStyle(style3);

		cell = row.createCell(6);
		cell.setCellValue("余量");
		cell.setCellStyle(style);
		String dataTime = "";
		for (int i = 0; i < list.size(); i++) {
			row = sheet.createRow(i + 1);
			cell = row.createCell(0); // 产品名称
			cell.setCellValue(list.get(i).getOut().getProduct().getName());
			cell.setCellStyle(style);

			cell = row.createCell(1); // 我司货号
			cell.setCellValue(list.get(i).getOut().getProduct().getCode());
			cell.setCellStyle(style);

			cell = row.createCell(2); // 产品分类
			if (list.get(i).getOut().getProduct().getProductType() == null) {
				cell.setCellValue("");
			} else {
				cell.setCellValue(list.get(i).getOut().getProduct().getProductType().getName());
			}
			cell.setCellStyle(style);

			if (list.get(i).getOut().getDatetime() != null) {
				dataTime = simpleDateFormat.format(list.get(i).getOut().getDatetime());
			}
			cell = row.createCell(3); // 出库时间
			cell.setCellValue(dataTime);
			cell.setCellStyle(style);

			cell = row.createCell(4); // 仓库名称
			cell.setCellValue(list.get(i).getOut().getWarehouse().getName());
			cell.setCellStyle(style);

			cell = row.createCell(5); // 出库数量
			cell.setCellValue(list.get(i).getOut().getRealbox());
			cell.setCellStyle(style3);

			cell = row.createCell(6); // 余量
			cell.setCellValue(list.get(i).getMount());
			cell.setCellStyle(style);

		}
		return workbook;
	}

	/**
	 * 创建入库明细workbook
	 * 
	 * @param workbook
	 * @param sheet
	 * @param list
	 * @return
	 */
	private static HSSFWorkbook createPutDetailList(HSSFWorkbook workbook, HSSFSheet sheet, List<DetailList> list) {
		// 表头
		HSSFRow row = sheet.createRow(0);
		HSSFCellStyle style = workbook.createCellStyle();
		HSSFCellStyle style3 = getStyle(workbook).get("middel_green");

		HSSFCell cell = row.createCell(0);
		cell.setCellValue("产品名称");
		cell.setCellStyle(style);

		cell = row.createCell(1);
		cell.setCellValue("我司货号");
		cell.setCellStyle(style);

		cell = row.createCell(2);
		cell.setCellValue("产品分类");
		cell.setCellStyle(style);

		cell = row.createCell(3);
		cell.setCellValue("入库时间");
		cell.setCellStyle(style);

		cell = row.createCell(4);
		cell.setCellValue("仓库名称");
		cell.setCellStyle(style);

		cell = row.createCell(5);
		cell.setCellValue("入库数量");
		cell.setCellStyle(style3);

		cell = row.createCell(6);
		cell.setCellValue("余量");
		cell.setCellStyle(style);
		String dataTime = "";
		for (int i = 0; i < list.size(); i++) {
			row = sheet.createRow(i + 1);
			cell = row.createCell(0); // 产品名称
			cell.setCellValue(list.get(i).getPut().getProduct().getName());
			cell.setCellStyle(style);

			cell = row.createCell(1); // 我司货号
			cell.setCellValue(list.get(i).getPut().getProduct().getCode());
			cell.setCellStyle(style);

			cell = row.createCell(2); // 产品分类
			if (list.get(i).getPut().getProduct().getProductType() == null) {
				cell.setCellValue("");
			} else {
				cell.setCellValue(list.get(i).getPut().getProduct().getProductType().getName());
			}
			cell.setCellStyle(style);

			if (list.get(i).getPut().getDatetime() != null) {
				dataTime = simpleDateFormat.format(list.get(i).getPut().getDatetime());
			}
			cell = row.createCell(3); // 入库时间
			cell.setCellValue(dataTime);
			cell.setCellStyle(style);

			cell = row.createCell(4); // 仓库名称
			cell.setCellValue(list.get(i).getPut().getWarehouse().getName());
			cell.setCellStyle(style);

			cell = row.createCell(5); // 入库数量
			cell.setCellValue(list.get(i).getPut().getRealbox());
			cell.setCellStyle(style3);

			cell = row.createCell(6); // 余量
			cell.setCellValue(list.get(i).getMount());
			cell.setCellStyle(style);
		}
		return workbook;
	}

	private static Map<String, HSSFCellStyle> getStyle(HSSFWorkbook workbook) {
		Map<String, HSSFCellStyle> map = new HashMap<>();
		HSSFCellStyle style = workbook.createCellStyle();
		// 居中
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		map.put("middel", style);
		// 居中-背景色红
		HSSFCellStyle style2 = workbook.createCellStyle();
		style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style2.setFillForegroundColor(HSSFColor.RED.index);// 设置背景色
		style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
		style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
		style2.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
		style2.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
		map.put("middel_red", style2);

		// 居中-背景色绿色
		HSSFCellStyle style3 = workbook.createCellStyle();
		style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style3.setFillForegroundColor(HSSFColor.GREEN.index);
		style3.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style3.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
		style3.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
		style3.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
		style3.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
		map.put("middel_green", style3);
		return map;
	}
}